** Alternative Capability Rankings (quality)
** This do-file is necessary for conducting robustness check shown in Table A24 

/* input 
${output_data}matched_exp_imp_hs6_junedecember_maquilaprice_restrict.dta
${output_data}quota_mex_weight_hs6.dta
*/

/* output
${output_data}mexico_for_analysis_04_07_hs6_restrict_quality.dta
${output_data}US_for_analysis_04_07_hs6_restrict_quality.dta
*/

*************************
*Step 1 Create Mexican downgrading 6-digit level data
*************************

*** Create Mexican downgrading 6-digit level using new ranking with MAIN PARTNER
use "${output_data}matched_exp_imp_hs6_junedecember_maquilaprice_restrict.dta", clear
keep rfc hs6 year cveunidad canto newclid2 value_exporter_importer
keep if year==2004
drop if newclid2==.
label var newclid2 "importer id"
label var value_exporter_importer "exports of hs6 between exporter and importer"
label var rfc "exporter id"
label var hs6 "hs6"

collapse (sum) canto value_exporter_importer, by(rfc newclid2 hs6 cve)
tostring cve, replace
gen hs2=substr(hs6,1,2)
drop if (hs2=="50" | hs2=="51" | hs2=="52" | hs2=="53" | hs2=="54" | hs2=="55" | hs2=="60" ) & (cve=="6" | cve=="7" | cve=="11" | cve=="12" | cve=="17" | cve=="18" | cve=="19" | cve=="20")
gen price=value_exporter_importer/canto
gen quantity = value_exporter_importer/price
gen new_value = log(quantity) + 4*log(price)
xi:reg new_value i.hs6
predict residuals, residuals
gen new_index=residuals/3
gen exporter_id=rfc
tostring newclid2, generate(importer_id)
gen hs6_cve_importer_id=hs6+"_"+cve+"_"+ importer_id
gen hs6_cve_exporter_id=hs6+"_"+cve+"_"+ rfc
save "${temp_data_quality}data_for_ranking_hs6_quality_yearly.dta", replace

use "${temp_data_quality}data_for_ranking_hs6_quality_yearly.dta", clear
gsort hs6_cve_importer_id -value_exporter_importer
by hs6_cve_importer_id: gen exporter_rank=_n
keep if exporter_rank==1
drop exporter_rank
rename exporter_id main_exporter
save "${temp_data_quality}data_for_ranking_hs6_main_exporter.dta", replace

use "${temp_data_quality}data_for_ranking_hs6_main_exporter.dta", clear
gsort hs6 cve -new_index newclid2
by hs6 cve: gen new_rank=_n
bys hs6 cve: egen hs6_cve_price=mean(price)
gen relative_new_value=price/hs6_cve_price
gsort hs6 cve -new_value newclid2
by hs6 cve: gen new_rank2=_n
assert new_rank==new_rank2
tostring new_rank, generate(new_rank_s)
gen hs6_cve_importer_id_new_rank=hs6_cve_importer_id+"_"+new_rank_s
drop new_rank_s
gen new_rank_partner=hs6_cve_exporter_id 
save "${temp_data_quality}data_for_ranking_hs6_q_rank.dta", replace

use "${output_data}matched_exp_imp_hs6_junedecember_maquilaprice_restrict.dta", replace
drop if newclid2==.
keep rfc hs6 year cveunidad canto newclid2 value_exporter_importer
collapse (sum) canto value_exporter_importer, by(rfc newclid2 hs6 cve year)
gen price=value_exporter_importer/canto
tostring newclid2, generate(importer_id)
tostring cve, replace
gen hs6_cve_importer_id=hs6+"_"+cve+"_"+ importer_id
gen hs6_cve_exporter_id=hs6+"_"+cve+"_"+ rfc
gen hs2=substr(hs6,1,2)
drop if (hs2=="50" | hs2=="51" | hs2=="52" | hs2=="53" | hs2=="54" | hs2=="55" | hs2=="60" ) & (cve=="6" | cve=="7" | cve=="11" | cve=="12" | cve=="17" | cve=="18" | cve=="19" | cve=="20")
save "${temp_data_quality}exporter_importer_hs6.dta", replace

* Prepare for making data on downgrading
use "${temp_data_quality}exporter_importer_hs6.dta", clear
tostring year, generate(year_s)
gen hs6_cve_exporter_id_year= hs6_cve_exporter_id+"_"+year_s
gsort hs6_cve_exporter_id_year -value_exporter_importer
by hs6_cve_exporter_id_year: gen importer_rank=_n
save "${temp_data_quality}prep_downgrading_1.dta", replace

* Obtain number of exporters in 2004 by product-unit
use "${temp_data_quality}prep_downgrading_1.dta", clear
keep if year==2004
keep hs6_cve_exporter_id hs6 cve
duplicates drop
gen n_exporters_2004=1
collapse (sum) n_exporters_2004, by(hs6 cve)
label var n_exporters_2004 "number of exporters in 2004 by product-unit"
save "${temp_data_quality}n_exporters_2004.dta", replace

* Obtain number of importers in 2004 by product-unit
use "${temp_data_quality}prep_downgrading_1.dta", clear
keep if year==2004
keep hs6_cve_importer_id hs6 cve
duplicates drop
gen n_importers_2004=1
collapse (sum) n_importers_2004, by(hs6 cve)
label var n_importers_2004 "number of importers in 2004 by product-unit"
save "${temp_data_quality}n_importers_2004.dta", replace

* Merge with data on number of importers/exporters in 2004 by product-unit
use "${temp_data_quality}prep_downgrading_1.dta", clear
merge m:1 hs6 cve using "${temp_data_quality}n_exporters_2004.dta"
drop _merge
merge m:1 hs6 cve using "${temp_data_quality}n_importers_2004.dta"
drop _merge
save "${temp_data_quality}downgrading.dta", replace

* Identify main partner in 2004 2007
foreach j in 2004 2007 {
use "${temp_data_quality}downgrading.dta", clear
keep if importer_rank==1 
keep if year==`j'
keep hs6_cve_importer_id hs6_cve_exporter_id
merge m:1 hs6_cve_importer_id using "${temp_data_quality}data_for_ranking_hs6_q_rank.dta"
drop if _merge!=3
rename hs6_cve_importer_id main`j'_id
rename new_rank new_rank`j'
rename relative_new_value relative_new_value`j'
keep hs6_cve_exporter_id main`j'_id new_rank`j' relative_new_value`j'
save "${temp_data_quality}main`j'.dta", replace
}

* Create data on 2004-2007 for Mexico 
use "${temp_data_quality}downgrading.dta", clear 
keep if year==2004
keep hs6 cve rfc hs6_cve_exporter_id n_*
duplicates drop
merge m:1 hs6_cve_exporter_id using "${temp_data_quality}main2004.dta"
drop if _merge==2
drop _merge
merge m:1 hs6_cve_exporter_id using "${temp_data_quality}main2007.dta"
drop if _merge==2
drop _merge
* Identify whether the ranking was downgrading
gen new_rank_d0704= new_rank2007-new_rank2004
gen i_new_rank_d0704=1 if new_rank_d0704>0
replace  i_new_rank_d0704=0 if new_rank_d0704<=0
replace  i_new_rank_d0704=. if new_rank_d0704==.
* Create data for analysis (Mexico)
keep hs6 cve main2004_id hs6_cve_exporter_id new_rank_d0704 new_rank_d0704 i_new_rank_d0704 new_rank2004 new_rank2007 n_importers_2004
gen newclid2=substr(main2004_id,12,length(main2004_id))
replace newclid2=subinstr(newclid2,"_","",.)
destring newclid2, replace
* Merge with quota data (for making a binding indicator)
merge m:1 hs6 using "${output_data}quota_mex_weight_hs6.dta"
drop if _merge==2
drop _merge
gen hs2=substr(hs6,1,2)

save "${output_data}mexico_for_analysis_04_07_hs6_quality_restrict.dta", replace


*************************
*Step 2 Create US upgrading 6-digit level data
*************************

** Create US upgrading 6-digit level using new ranking with MAIN PARTNER
use "${temp_data_quality}data_for_ranking_hs6_quality_yearly.dta", clear
gsort hs6_cve_exporter_id -value_exporter_importer
by hs6_cve_exporter_id: gen importer_rank=_n
keep if importer_rank==1
drop importer_rank
rename importer_id main_importer
gsort hs6 cve -new_index rfc
by hs6 cve: gen new_rank=_n
gsort hs6 cve -new_value rfc
by hs6 cve: gen new_rank2=_n
bys hs6 cve: egen hs6_cve_price=mean(price)
gen new_value_relative=price/hs6_cve_price
assert new_rank==new_rank2
tostring new_rank, generate(new_rank_s)
gen hs6_cve_exporter_id_new_rank=hs6_cve_exporter_id+"_"+new_rank_s
drop new_rank_s
gen p_partner=hs6_cve_importer_id 
save "${temp_data_quality}data_for_exp_ranking_hs6_q_rank.dta", replace

* Prepare for making data on upgrading
use "${temp_data_quality}exporter_importer_hs6.dta", clear
tostring year, generate(year_s)
gen hs6_cve_importer_id_year= hs6_cve_importer_id+"_"+year_s
gsort hs6_cve_importer_id_year -value_exporter_importer
by hs6_cve_importer_id_year: gen exporter_rank=_n
save "${temp_data_quality}prep_upgrading_1.dta", replace

* Obtain number of exporters in 2004 by product-unit
use "${temp_data_quality}prep_upgrading_1.dta", clear
keep if year==2004
keep hs6_cve_exporter_id hs6 cve
duplicates drop
gen n_exporters_2004=1
collapse (sum) n_exporters_2004, by(hs6 cve)
label var n_exporters_2004 "number of exporters in 2004 by product-unit"
save "${temp_data_quality}num_exporters_2004.dta", replace

* Obtain number of importers in 2004 by product-unit
use "${temp_data_quality}prep_upgrading_1.dta", clear
keep if year==2004
keep hs6_cve_importer_id hs6 cve
duplicates drop
gen n_importers_2004=1
collapse (sum) n_importers_2004, by(hs6 cve)
label var n_importers_2004 "number of importers in 2004 by product-unit"
save "${temp_data_quality}num_importers_2004.dta", replace

* Merge with data on number of importers/exporters in 2004 by product-unit
use "${temp_data_quality}prep_upgrading_1.dta", clear
merge m:1 hs6 cve using "${temp_data_quality}num_exporters_2004.dta"
drop _merge
merge m:1 hs6 cve using "${temp_data_quality}num_importers_2004.dta"
drop _merge
save "${temp_data_quality}upgrading.dta", replace

* Identify main partner in 2004 2007
foreach j in 2004 2007 {
use "${temp_data_quality}upgrading.dta", clear
keep if exporter_rank==1 
keep if year==`j'
keep hs6_cve_importer_id hs6_cve_exporter_id
merge m:1 hs6_cve_exporter_id using "${temp_data_quality}data_for_exp_ranking_hs6_q_rank.dta"
drop if _merge!=3
rename hs6_cve_exporter_id main`j'_id
rename new_rank new_rank`j'
keep hs6_cve_importer_id main`j'_id new_rank`j'
save "${temp_data_quality}main`j'_US.dta", replace
}

* Create data on 2004-2007 for US
use "${temp_data_quality}upgrading.dta", clear 
keep if year==2004
keep hs6 cve newclid2 hs6_cve_importer_id n_*
duplicates drop
merge m:1 hs6_cve_importer_id using "${temp_data_quality}main2004_US.dta"
drop if _merge==2
drop _merge
merge m:1 hs6_cve_importer_id using "${temp_data_quality}main2007_US.dta"
drop if _merge==2
drop _merge
* Identify whether the ranking was upgrading
gen e_new_rank_d0704= new_rank2007-new_rank2004
gen e_i_new_rank_d0704=1 if e_new_rank_d0704<0
replace  e_i_new_rank_d0704=0 if e_new_rank_d0704>=0
replace  e_i_new_rank_d0704=. if e_new_rank_d0704==.
* Create data for analysis (US)
keep hs6 cve main2004_id hs6_cve_importer_id newclid2 e_new_rank_d0704 e_i_new_rank_d0704  new_rank2004 new_rank2007 n_exporters_2004
* Merge with quota data (for making a binding indicator)
merge m:1 hs6 using "${output_data}quota_mex_weight_hs6.dta"
drop if _merge==2
drop _merge

gen hs2=substr(hs6,1,2)

save "${output_data}US_for_analysis_04_07_hs6_quality_restrict.dta", replace
